"""
Case Type   : 二级分区
Case Name   : 二级分区表编码兼容测试
Create At   : 2022/2/26
Owner       : opentestcase012
Description :
    1.创建库，指定编码UTF8和数据库类型
    2.创建二级分区表
    3.插入特殊符号数据
    4.查询数据
    5.指定编码查询数据
    6.插入特殊数据
    7.查询数据
    8.设置session编码为gb18030
    9.copy to/from导入导出数据
    10.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
    7.成功
    8.成功
    9.成功
    10.清理成功
History     :
"""
import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class Subpartition(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.primaryNode = Node('PrimaryDbUser')
        self.constant = Constant()
        self.t_name = 't_subpartition_0353'
        self.db_name = 'db_subpartition_0353'
        self.copy_file = os.path.join(macro.DB_INSTANCE_PATH,
                                      'f_subpartition_0353.dat')

    def test_subpartition(self):
        text = '-----step1:创建库，指定编码UTF8和数据库类型 expect:成功-----'
        self.log.info(text)
        create_sql = f"drop database if exists {self.db_name};" \
            f"create database {self.db_name} encoding 'UTF8' " \
            f"template = template0;" \
            f"drop database if exists {self.db_name};" \
            f"create database {self.db_name} dbcompatibility='B';"
        msg = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(msg)
        self.assertEqual(2, msg.count(self.constant.DROP_DATABASE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual(2, msg.count(self.constant.CREATE_DATABASE_SUCCESS),
                         '执行失败:' + text)

        text = '-----step2:创建二级分区表 expect:成功-----'
        self.log.info(text)
        create_sql = f"drop table if exists {self.t_name};" \
            f"create table {self.t_name} (col_1 text, col_2 int not null , " \
            f"col_3 varchar2 ( 30 ) , col_4 int )with(fillfactor=80)" \
            f"partition by range (col_1) subpartition by hash (col_2)" \
            f"( partition p_range_1 values less than( -10 )" \
            f"  ( subpartition p_hash_1_1 , subpartition p_hash_1_2 ," \
            f"    subpartition p_hash_1_3)," \
            f"  partition p_range_2 values less than( 20 )," \
            f"  partition p_range_3 values less than( 30)" \
            f"  ( subpartition p_hash_3_1 , subpartition p_hash_3_2 ," \
            f"    subpartition p_hash_3_3)," \
            f"    partition p_range_4 values less than( 50)" \
            f"  ( subpartition p_hash_4_1 , subpartition p_hash_4_2 ," \
            f"    subpartition t_subpartition_0350)," \
            f"  partition p_range_5 values less than( maxvalue )" \
            f") enable row movement;"
        msg = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(msg)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, msg, '执行失败:' + text)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, msg, '执行失败:' + text)

        text = '-----step3:插入特殊符号数据 expect:成功-----'
        self.log.info(text)
        sql = f"insert into {self.t_name} values('😠',1,1,1);" \
            f"insert into {self.t_name} values('Emoji表情:😠!!',2,2,2);"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertEqual(2, msg.count(self.constant.INSERT_SUCCESS_MSG),
                         '执行失败:' + text)

        text = '-----step4:查询数据 expect:成功-----'
        self.log.info(text)
        sql = f"select * from {self.t_name};"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn('2 rows', msg, '执行失败:' + text)
        self.assertIn('😠', msg, '执行失败:' + text)
        self.assertIn('Emoji表情:😠!!', msg, '执行失败:' + text)

        text = '-----step5:指定编码查询数据 expect:成功-----'
        self.log.info(text)
        sql = f"select convert_to(col_1, 'UTF8') from {self.t_name};"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn('x456d6f6a69e8a1a8e683853af09f98a02121', msg,
                      '执行失败:' + text)
        self.assertIn('xf09f98a0', msg, '执行失败:' + text)

        text = '-----step6:插入特殊数据 expect:成功-----'
        self.log.info(text)
        sql = f"insert into {self.t_name} values('𫝆𫝯𫝩𫝮𫝱',4,4,4);"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, msg, '执行失败:' + text)

        text = '-----step7:查询数据 expect:成功-----'
        self.log.info(text)
        sql = f"select * from {self.t_name};"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn('3 rows', msg, '执行失败:' + text)
        self.assertIn('𫝆𫝯𫝩𫝮𫝱', msg, '执行失败:' + text)

        text = '-----step8:设置session编码为gb18030; expect:成功-----'
        self.log.info(text)
        sql = f"set session client_encoding to gb18030;"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn(self.constant.SET_SUCCESS_MSG, msg, '执行失败:' + text)

        text = '-----step9:copy to/from导入导出数据; expect:成功-----'
        self.log.info(text)
        sql = f"copy {self.t_name} to '{self.copy_file}';" \
            f"copy {self.t_name} from '{self.copy_file}';"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertEqual(2, msg.count('COPY 3'), '执行失败:' + text)

    def tearDown(self):
        self.log.info('-----step10:清理环境-----')
        text_1 = '-----删除表和库 expect:成功-----'
        self.log.info(text_1)
        del_cmd = f'drop table if exists {self.t_name};' \
            f'drop database if exists {self.db_name};'
        del_msg = self.pri_sh.execut_db_sql(del_cmd)
        self.log.info(del_msg)

        text_2 = '-----删除copy文件 expect:成功-----'
        self.log.info(text_2)
        rm_cmd = f'rm -rf {self.copy_file};cat {self.copy_file};'
        rm_msg = self.primaryNode.sh(rm_cmd).result()
        self.log.info(rm_msg)

        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, del_msg,
                      '执行失败:' + text_1)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, del_msg,
                      '执行失败:' + text_1)
        self.assertIn(self.constant.NO_FILE_MSG, rm_msg, '执行失败:' + text_2)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
